﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GAMS.MODEL;
using System.Globalization;


namespace KPIS.GERP.GAMS.DAL
{
    public sealed class D_ACM_DepreciationType
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();
        D_ACM_GENERAL_Ledger gl = new D_ACM_GENERAL_Ledger();

        M_ACM_Depliciate DP = new M_ACM_Depliciate();
        DateTime Updated;
        DateTime AssetDate;
        Decimal raka;
        int year;
        Decimal DepAmount;
        Decimal SumAmount;
        Decimal SumDepreciation;
        int? GetDpYear;
        public IList<M_ACM_Depliciate> SumDepreciationType(string selectMonth, string SelectBgmYear, string strEmpSeq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_ACM_Depliciate> infoList = new List<M_ACM_Depliciate>();

            try
            {
                dbManager.Open();
                if (SelectBgmYear != null && selectMonth != null)
                {
                    string strTranSeq = null;
                    dbManager.CreateParameters(2);
                    dbManager.AddParameters(0, "@selectMonth", selectMonth);
                    dbManager.AddParameters(1, "@bgm_year", SelectBgmYear);
                    // SELECT a.AssetId as AssetId ,a.Price as Price,a.LifeYear as  LifeYear,b.CreatedWhen as  CreatedWhen ,b.CalculatedDate as CalculatedDate from IcmAsset a left join  IcmAssetDepreciation b  on a.AssetId=b.AssetId where a.IsLowerAsset = 'False'  AND MONTH(AssetDate)=@selectMonth
                    strSQL = "SELECT a.*, pje.bgm_source_seq, rg.CumdepreAccountId, rg.DepreAccountId"
                        + " from IcmAsset a"
                        + " left join IcmMasResourceGroup rg on a.ResGrpId = rg.ResGrpId"
                        + " left join bgm_project_expense pje on a.ProjExpId = pje.bgm_proj_exp_seq"
                        + " where a.IsLowerAsset=0"
                        + " and a.RecordStatus <> 'D'";
                    //+ " group by pje.bgm_source_seq, rg.CumdepreAccountId, rg.DepreAccountId";
                    drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                    if (drc.Count > 0)
                    {
                        DateTime dtCalculateDate = new DateTime(Convert.ToInt32(SelectBgmYear) - (Convert.ToInt32(selectMonth) < 10 ? 543 : 544), Convert.ToInt32(selectMonth), 1).AddMonths(1).AddDays(-1);
                        strSQL = "SELECT max(CalculatedDate) from IcmAssetDepreciation where RecordStatus not like 'D'";
                        DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                        if (dtb.Rows.Count > 0)
                        {
                            if (dtb.Rows[0][0].ToString().Length > 0)
                            {
                                DateTime dtLastCalculateDate = Convert.ToDateTime(dtb.Rows[0][0]);

                                if (dtCalculateDate <= dtLastCalculateDate)
                                {
                                    return null;
                                }
                            }
                        }

                        for (int i = 0; i < drc.Count; i++)
                        {
                            M_ACM_Depliciate infoAdd = new M_ACM_Depliciate();
                            infoAdd.asset_id = Convert.ToInt32(drc[i]["AssetId"]);
                            infoAdd.asset_code = drc[i]["AssetCode"].ToString();
                            infoAdd.asset_date = Convert.ToDateTime(drc[i]["AssetDate"]);
                            infoAdd.price = Convert.ToDouble(drc[i]["Price"]);
                            infoAdd.asset_name = drc[i]["AssetName"].ToString();
                            infoAdd.serial_number = drc[i]["SerialNumber"].ToString();
                            infoAdd.SumDepreciation = GetDepriciate(Convert.ToInt32(drc[i]["AssetId"]), "DPMonth", dtCalculateDate);
                            infoAdd.created_by = Convert.ToInt32(strEmpSeq);
                            infoAdd.created_when = DateTime.Now;
                            infoAdd.calculate_date = dtCalculateDate;
                            infoAdd.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                            infoAdd.cum_depre_account_id = cn.ConvertNullInt(drc[i]["CumdepreAccountId"]);
                            infoAdd.depre_account_id = cn.ConvertNullInt(drc[i]["DepreAccountId"]);
                            infoList.Add(infoAdd);

                            if (infoAdd.cum_depre_account_id == null || infoAdd.depre_account_id == null)
                            {
                                return null;
                            }
                        }

                        for (int i = 0; i < infoList.Count; i++)
                        {
                            Insert_DB_CR(infoList[i]);
                            if (i == 0)
                            {
                                M_ACM_GENERAL_Ledger model = new M_ACM_GENERAL_Ledger();
                                model.tran_code = gl.GetNextTranCode(infoList[i].bgm_source_seq.ToString(), SelectBgmYear, "G");
                                model.tran_name = "ลงบัญชีค่าเสื่อมราคา " + SelectBgmYear + "/" + selectMonth;
                                model.bgm_source_seq = cn.ConvertNullInt(infoList[i].bgm_source_seq);
                                model.tran_date = dtCalculateDate;
                                model.bgm_year = cn.ConvertNullInt(SelectBgmYear);
                                model.ref_no = null;
                                model.ref_type = null;
                                model.remark = null;
                                model.tran_type = "G";
                                model.work_area_seq = null;
                                model.fiscal_period = SelectBgmYear + "/" + selectMonth;
                                model.tran_grp_seq = gl.GetNextTranGrpSeq();
                                model.record_status = "W";
                                model.created_by = Convert.ToInt32(strEmpSeq);
                                model.created_when = DateTime.Now;

                                strTranSeq = gl.Insert_Transaction(model, true);
                            }

                            if (string.IsNullOrEmpty(strTranSeq))
                            {
                                return infoList;
                            }

                            SumAmount += infoList[i].SumDepreciation ?? 0;

                            if (i == infoList.Count - 1 || infoList[i].cum_depre_account_id != (infoList[i + 1].cum_depre_account_id ?? 0))
                            {
                                M_ACM_GENERAL_Ledger model = new M_ACM_GENERAL_Ledger();
                                model.tran_seq = Convert.ToInt32(strTranSeq);
                                model.account_seq = infoList[i].depre_account_id;
                                model.dr_or_cr = "Dr";
                                model.amount = (double)SumAmount;
                                model.record_status = "W";
                                model.created_by = Convert.ToInt32(strEmpSeq);
                                model.created_when = DateTime.Now;

                                gl.Insert_DB_CR(model);

                                M_ACM_GENERAL_Ledger model2 = new M_ACM_GENERAL_Ledger();
                                model2.tran_seq = Convert.ToInt32(strTranSeq);
                                model2.account_seq = infoList[i].cum_depre_account_id;
                                model2.dr_or_cr = "Cr";
                                model2.amount = (double)SumAmount;
                                model2.record_status = "W";
                                model2.created_by = Convert.ToInt32(strEmpSeq);
                                model2.created_when = DateTime.Now;

                                gl.Insert_DB_CR(model2);

                                SumAmount = 0;
                            }
                        }
                    }
                }
                else
                {
                    strSQL = "SELECT * from ICMAsset  where IsLowerAsset=0";
                    drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                    dbManager.Dispose();
                    if (drc.Count > 0)
                    {
                        for (int i = 0; i < drc.Count; i++)
                        {
                            M_ACM_Depliciate info = new M_ACM_Depliciate();
                            info.asset_id = Convert.ToInt32(drc[i]["AssetId"]);
                            info.asset_code = drc[i]["AssetCode"].ToString();
                            info.asset_date = Convert.ToDateTime(drc[i]["AssetDate"]);
                            info.price = Convert.ToDouble(drc[i]["Price"]);
                            info.asset_name = drc[i]["AssetName"].ToString();
                            info.serial_number = drc[i]["SerialNumber"].ToString();
                            info.dp = GetSumDepAmount(null, null, null, Convert.ToInt32(drc[i]["AssetId"])) ?? 0;

                            infoList.Add(info);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                //if (dbManager != null)
                //{
                //    dbManager.Dispose();
                //}
            }

            return infoList;
        }

        public Nullable<Decimal> GetSumDepAmount(string Month, string Year, string NameStr, int assetid)
        {

            string strSQL;
            DataRowCollection drc = null;
            dbManager.Open();
            if (Month == null && NameStr == null)
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@assetid", assetid);

                strSQL = "SELECT sum(coalesce(b.DepAmount, 0)) as DepAmount from IcmAssetDepreciation b where b.AssetId=@assetid ";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    if (drc[0][0].ToString().Length > 0)
                    {
                        SumAmount = Convert.ToDecimal(drc[0][0]);
                    }
                    else
                    {
                        SumAmount = 0;
                    }
                    //for (int i = 0; i < drc.Count; i++)
                    //{
                    //    SumAmount = 0;
                    //}
                }
                else
                {
                    SumAmount = 0;
            }
            }
            else
            {
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@selectMonth", Month);
                dbManager.AddParameters(1, "@selectYear", Year);
                dbManager.AddParameters(2, "@assetid", assetid);
                strSQL = "SELECT sum(coalesce(b.DepAmount, 0)) as DepAmount"
                    + " from IcmAsset a"
                    + " left join IcmAssetDepreciation b  on a.AssetId=b.AssetId"
                    + " where a.IsLowerAsset = 0"
                    + " AND MONTH(AssetDate)=@selectMonth"
                    + " AND YEAR(AssetDate)=@selectYear"
                    + " AND a.AssetId=@assetid";
                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        if (drc[0][0].ToString().Length > 0)
                        {
                            SumAmount += Convert.ToDecimal(drc[0][0]);
                        }
                        else
                        {
                            SumAmount += 0;
                        }

                        //SumAmount = Convert.ToDecimal(drc[i]["DepAmount"]);
                        //if (SumAmount == 0)
                        //{
                        //    SumAmount = 0;
                        //}
                        //else
                        //{
                        //    SumAmount = Convert.ToInt32(drc[i]["DepAmount"]);
                        //}
                    }
                }
            }

            dbManager.Dispose();
            return SumAmount;
        }

        public decimal GetDepriciate(int assetid, string ProcessName, DateTime dtCalculateDate)
        {
            DateTime LastCalDate = new DateTime();
            decimal dDeprePerDay = 0;
            decimal dDepre = 0;
            string strSQL;
            dbManager.Open();
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@AssetId", assetid);
            DataRowCollection drc = null;
            //IcmAsset a inner join  IcmMasDepreciationType b  on a.DepreciationTypeId=b.DpTypeId  where a.IsLowerAsset = 'False' 
            strSQL = "SELECT a.AssetId,a.Price,a.LifeYear"
                + ",b.CalculatedDate,a.AssetDate,b.DepAmount as DepAmount"
                + " from IcmAsset a"
                + " Left join (select AssetId"
                    + ", sum(coalesce(DepAmount, 0)) as DepAmount"
                    + ", max(CalculatedDate) as CalculatedDate"
                    + " from IcmAssetDepreciation"
                    + " where RecordStatus not like 'D'"
                    + " group by AssetId"
                + ") b on a.AssetId=b.AssetId"
                + " where a.AssetId=@AssetId";
            drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
            if (drc.Count > 0)
            {
                for (int i = 0; i < drc.Count; i++)
                {
                    if (!string.IsNullOrEmpty(drc[i]["CalculatedDate"].ToString()) && ProcessName == "DPMonth")//check value daterecive 
                    {
                        AssetDate = Convert.ToDateTime(drc[i]["AssetDate"].ToString());//เวลา
                        LastCalDate = Convert.ToDateTime(drc[i]["CalculatedDate"].ToString());//เวลา

                        raka = Convert.ToInt32(drc[i]["Price"].ToString());
                        year = Convert.ToInt32(drc[i]["LifeYear"].ToString());
                        DepAmount = Convert.ToDecimal(drc[i]["DepAmount"].ToString());
                    }
                    else
                    {
                        AssetDate = Convert.ToDateTime(drc[i]["AssetDate"].ToString());
                        LastCalDate = Convert.ToDateTime(drc[i]["AssetDate"].ToString());//เวลา

                        raka = Convert.ToInt32(drc[i]["Price"].ToString());
                        year = Convert.ToInt32(drc[i]["LifeYear"].ToString());
                            DepAmount = 0;
                    }
                }
            }

            int nLifeTimeDayCount = (int)Math.Ceiling((AssetDate.AddYears(year) - AssetDate).TotalDays);
            int nCalculateDayCount = (int)Math.Ceiling((dtCalculateDate - LastCalDate).TotalDays);

            if (nLifeTimeDayCount > 0)
            {
                dDeprePerDay = (decimal)((raka - 1) / nLifeTimeDayCount);
            }

            dDepre = dDeprePerDay * nCalculateDayCount;

            if (dDepre > raka - DepAmount - 1)
            {
                dDepre = raka - DepAmount - 1;
            }

            return Math.Round(dDepre, 2);

            //if (AssetDate.Month != DateIntable.Month && ProcessName == "DPMonth")
            //{
           
            
            //    DateTime CountAssetDate = AssetDate.AddYears(year);// วัน/เดือน/ปี + จำนวน lifeyear
            //    TimeSpan b = CountAssetDate.Subtract(DateTime.Now);//จำนวนวันที่รับเข้าคลังหรือวันที่คำนวณ เอามาหาจำนวนวันทั้งหมดกับ วันปัจจุบัน
            //    TimeSpan s = CountAssetDate.Subtract(AssetDate);//
            //    int CountDateNow = (DateTime.Now).Day;//นับจำนวนวันปัจจุบัน
            //    int SumDateNow = (DateTime.MaxValue).Day;// จำนวนวันทั้งหมด- จำนวนวันปัจจุบัน

            //    int TestSumDateNow = System.DateTime.DaysInMonth(DateIntable.Year, DateIntable.Month);// จำนวนวันทั้งหมด- จำนวนวันปัจจุบัน
                
             
            //    int b2 = b.Days;

           
            //    int SumOfDay = raka / s.Days; // Convert.ToInt32(a);
            //    int sum = TestSumDateNow * SumOfDay;//nวัน * ค่าเสื่อมรายวัน
            //    return sum;
            //}
            

            // if (ProcessName == "DpYear")//บันทึกบัญชีค่าเสื่อมรายปี
            //{
                
            //    DateTime CountAssetDate = AssetDate.AddYears(year);// วัน/เดือน/ปี + จำนวน lifeyear
             
            //    TimeSpan b = CountAssetDate.Subtract(DateTime.Now);//จำนวนวันที่รับเข้าคลังหรือวันที่คำนวณ เอามาหาจำนวนวันทั้งหมดกับ วันปัจจุบัน
            //    TimeSpan s = CountAssetDate.Subtract(AssetDate);//
            //    int CountDateNow = (DateTime.Now).Day;//นับจำนวนวันปัจจุบัน
            //    int SumDateNow = (DateTime.MaxValue).Day;// จำนวนวันทั้งหมด- จำนวนวันปัจจุบัน

            //    //int TestSumDateNow = System.DateTime.DaysInMonth(DateIntable.Year, DateIntable.Month);// จำนวนวันทั้งหมด- จำนวนวันปัจจุบัน


            //    int b2 = b.Days;

            //    //int SumOfDay = ((raka - 1) - (Convert.ToInt32(DepAmount))) / CountAssetDate.Day; // Convert.ToInt32(a);
            //    Decimal SumOfDay = Convert.ToDecimal(raka) / Convert.ToDecimal(s.Days); // Convert.ToInt32(a);
            //    int sum = 365 * Convert.ToInt32(SumOfDay);//nวัน * ค่าเสื่อมรายวัน
                    
              
            //    return sum;
            //}
            //else
            //{
            //    int sum = 0;
            //    return sum;
            //}

        }

        public string GetCheckday(int SelectBgmYear)
        {
            string SumCk;
            CultureInfo UsaCulture = new CultureInfo("en-US");
            DataRowCollection drc = null;
            string StrSQL;
            int BgmYear = (SelectBgmYear - 543);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@SelectMonth", BgmYear);
                StrSQL = "select * from acm_transection a left join acm_transection_dtls b on  a.tran_seq=b.tran_seq where YEAR(a.created_when)=@SelectMonth ";
                drc = dbManager.ExecuteDataSet(CommandType.Text, StrSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    SumCk = "1";
                    return SumCk;
                    
                }
                else
                {
                    SumCk = "0";
                    return SumCk;
                }
            }
           
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void Insert_DB_CR(M_ACM_Depliciate info)//acm_transection_dtls
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);
                dbManager.AddParameters(0, "@AssetId", info.asset_id);
                dbManager.AddParameters(1, "@CalculateDate", info.calculate_date);
                dbManager.AddParameters(2, "@DepAmount", info.SumDepreciation);

                dbManager.AddParameters(3, "@RecordStatus", 'U');
                dbManager.AddParameters(4, "@UpdatedBy", info.created_by);
                dbManager.AddParameters(5, "@UpdatedWhen", info.created_when);

                strSQL = " INSERT INTO IcmAssetDepreciation "
                        + "(AssetId,CalculatedDate,DepAmount,RecordStatus,CreatedBy,CreatedWhen)"
                        + " VALUES (@AssetId,@CalculateDate,@DepAmount,@RecordStatus,@UpdatedBy,@UpdatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //public void InsertDPAccount(string SelectBgmYear, string selectMonth, int nEmpSeq)
                        //{
        //    if (Updated.Year != DateTime.Now.Year)
        //    {
        //        string strSQL;
                    
        //        try
        //        {
        //            dbManager.Open();
        //            DataRowCollection drc = null;
        //            D_ACM_Finance_Payment d_acm_finance_payment = new D_ACM_Finance_Payment();
        //            M_ACM_GENERAL_Ledger infoDtls = new M_ACM_GENERAL_Ledger();
        //            D_ACM_GENERAL_Ledger gl = new D_ACM_GENERAL_Ledger();
        //            //////// หมายเหตุต้องหา ค่า sum  โดยการ 
        //            //strquery = "";

        //            //strSQL = "select  sum(DepAmount) as DepAmount,b.ResGrpId as ResGrpId,b.Name as Name,a.AssetId as AssetId ,a.UpdatedWhen as UpdatedWhen from IcmAsset a left join  IcmMasResourceGroup  b on a.ResGrpId=b.ResGrpId  left join  IcmAssetDepreciation c on a.AssetId=c.AssetId where a.AssetId=c.AssetId";
        //            strSQL = "select a.Price as Price ,a.ResGrpId as ResGrpId, a.Assetid as AssetId,a.LifeYear as LifeYear,a.UpdatedWhen as UpdatedWhen from  IcmAsset a  left join IcmAssetDepreciation b on a.AssetId=b.AssetId  where IsLowerAsset=0 ";
        //            drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
        //            if (drc.Count > 0)
        //            {
        //                for (int i = 0; i < drc.Count; i++)
        //                {
        //                    GetDpYear = GetDepriciate(Convert.ToInt32(drc[i]["AssetId"]), "DpYear", );
        //                    DepAmount = Convert.ToDecimal(GetDpYear);
        //                }

        //                infoDtls.tran_type = "G";
        //                infoDtls.tran_code = gl.GetNextTranCode(selectMonth, SelectBgmYear, "G");
        //                infoDtls.amount = Convert.ToDouble(GetDpYear);
        //                infoDtls.tran_name = "บันทึกรายการค่าเสื่อมประจำปี" + DateTime.Now.Year;
        //                infoDtls.fiscal_period = (Convert.ToInt32(SelectBgmYear) - 543) + "/" + selectMonth;
        //                infoDtls.remark = null;
        //                infoDtls.record_status = "W";
        //                infoDtls.created_by = nEmpSeq;
        //                infoDtls.created_when = DateTime.Now;
        //                string strTranSeq = d_acm_finance_payment.Insert_Transaction(infoDtls);
        //                string[] arr2 = { "Dr", "Cr" };

        //                M_ACM_GENERAL_Ledger infoMas = new M_ACM_GENERAL_Ledger();
        //                for (int i = 0; i < arr2.Length; i++)
        //                {
        //                    infoMas.tran_seq = Convert.ToInt32(strTranSeq);
        //                    infoMas.dr_or_cr = arr2[i];
        //                    infoMas.amount = Convert.ToDouble(GetDpYear);
        //                    infoMas.record_status = "W";
        //                    infoMas.created_by = nEmpSeq;
        //                    infoMas.created_when = DateTime.Now;

        //                    d_acm_finance_payment.Insert_DB_CR(infoMas);
        //                }

        //                dbManager.Dispose();
               
        //                for (int i = 0; i < drc.Count; i++)
        //                {
        //                    DepAmount = (Convert.ToDecimal(drc[i]["Price"].ToString()) - (Convert.ToDecimal(GetDpYear)));
        //                    int AssetId = Convert.ToInt32(drc[i]["Assetid"].ToString());
        //                    int ResGrpId = Convert.ToInt32(drc[i]["ResGrpId"].ToString());

        //                    try
        //                    {
        //                        dbManager.Open();
        //                        dbManager.CreateParameters(5);

        //                        dbManager.AddParameters(0, "@AssetId", AssetId);
        //                        dbManager.AddParameters(1, "@SumAmontDp", DepAmount);
        //                        dbManager.AddParameters(2, "@ResGrpId", ResGrpId);
        //                        dbManager.AddParameters(3, "@updated_when", DateTime.Now);
        //                        dbManager.AddParameters(4, "@updated_by", nEmpSeq);

        //                        strSQL = "UPDATE IcmAsset"
        //                            + "  SET Price=@SumAmontDp "

        //                            + ", UpdatedBy = @updated_by"
        //                            + ", UpdatedWhen = @updated_when"
        //                            + " WHERE AssetId = @AssetId  AND ResGrpId = @ResGrpId";

        //                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
        //                    }

        //                    catch (Exception ex)
        //                    {
        //                        throw (ex);
        //                    }
        //                    finally
        //                    {
        //                        dbManager.Dispose();
        //                    }
        //                }
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            throw (ex);
        //        }
        //        finally
        //        {
        //            //dbManager.Dispose();
        //        }
        //    }
        //}
    }
}